﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using SqlOperate;
using nspLable.Function;
using System.Web;
using System.Text;

/// <summary>
/// R_Supply 的摘要说明
/// </summary>
namespace Product
{
    public class RSupply : ISupply
    {
        public static string ConnectionString = ConfigurationManager.ConnectionStrings["sqlData"].ConnectionString;
        #region ISupply 成员

        /// <summary>
        /// 添加供应信息
        /// </summary>
        /// <param name="eui">供应信息对象</param>
        /// <returns></returns>
        public int Insert(TSupply eui, out int intReturn)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {                
                new SqlParameter("@Return", SqlDbType.Int),
                new SqlParameter("@UserID",eui.UserID),
                new SqlParameter("@IndType", eui.IndType),
				new SqlParameter("@Name", eui.Name),
                new SqlParameter("@Key", eui.Key),
                new SqlParameter("@Info", eui.Info),
                new SqlParameter("@Thumbnail", eui.Thumbnail),
                new SqlParameter("@Picture_1", eui.Picture_1),
                new SqlParameter("@Picture_2", eui.Picture_2),
                new SqlParameter("@Picture_3", eui.Picture_3),
				new SqlParameter("@ProType", eui.ProType),
                new SqlParameter("@Place", eui.Place),
                new SqlParameter("@Brand", eui.Brand),
				new SqlParameter("@Model", eui.Model),
                new SqlParameter("@Price", eui.Price),
                new SqlParameter("@Quantity", eui.Quantity),
				new SqlParameter("@Units", eui.Units),
                new SqlParameter("@Minimum", eui.Minimum),
                new SqlParameter("@Deadline", eui.Deadline),
                new SqlParameter("@StartDate", eui.StartDate),
                new SqlParameter("@EndDate", eui.EndDate)
             };
            intReturn = 0;
            commandParameters[0].Direction = ParameterDirection.Output;
            int ReRows = SqlHelper.ExecuteNonQuery(CommandType.StoredProcedure, "SP_C_Supply_Add", commandParameters);
            if (ReRows >0)
                intReturn = Convert.ToInt32(commandParameters[0].Value);
            return ReRows;
        }


        /// <summary>
        /// 修改供应信息
        /// </summary>
        /// <param name="eui">供应信息对象<</param>
        /// <returns></returns>
        public int Update(TSupply eui)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                 new SqlParameter("@ID", eui.ID),
                new SqlParameter("@IndType", eui.IndType),
				new SqlParameter("@Name", eui.Name),
                new SqlParameter("@Key", eui.Key),
                new SqlParameter("@Info", eui.Info),
                new SqlParameter("@Thumbnail", eui.Thumbnail),
                new SqlParameter("@Picture_1", eui.Picture_1),
                new SqlParameter("@Picture_2", eui.Picture_2),
                new SqlParameter("@Picture_3", eui.Picture_3),
				new SqlParameter("@ProType", eui.ProType),
                new SqlParameter("@Place", eui.Place),
                new SqlParameter("@Brand", eui.Brand),
				new SqlParameter("@Model", eui.Model),
                new SqlParameter("@Price", eui.Price),
                new SqlParameter("@Quantity", eui.Quantity),
				new SqlParameter("@Units", eui.Units),
                new SqlParameter("@Minimum", eui.Minimum),
                new SqlParameter("@Deadline", eui.Deadline),
                new SqlParameter("@StartDate", eui.StartDate),
                new SqlParameter("@EndDate", eui.EndDate),
                new SqlParameter("@Remark", eui.Remark),
                new SqlParameter("@Document", eui.Document)
            };
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "SP_C_Supply_Update", commandParameters);
        }
        /// <summary>
        /// 修改审核信息
        /// </summary>
        /// <param name="eui"></param>
        /// <returns></returns>
        public int UpdateVerify(TSupply eui)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@ID", eui.ID),
                new SqlParameter("@Verify", eui.Verify)
               
             };
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "SP_C_Supply_UpdateVerify", commandParameters);
        }
        /// <summary>
        /// 修改推荐信息
        /// </summary>
        /// <param name="eui"></param>
        /// <returns></returns>
        public int UpdateRecommend(TSupply eui)
        {
            SqlParameter[] commandParameters = new SqlParameter[]
            {
                new SqlParameter("@ID", eui.ID),
                new SqlParameter("@Recommend", eui.Recommend)
               
             };
            return SqlHelper.ExecuteNonQuery(ConnectionString, CommandType.StoredProcedure, "SP_C_Supply_UpdateRecommend", commandParameters);
        }


        /// <summary>
        /// 根据ID删除供应信息
        /// </summary>
        /// <param name="ID">供应ID</param>
        /// <returns></returns>
        public int Delete(int ID)
        {
            SqlParameter[] param = new SqlParameter[] 
            {
                new SqlParameter("@strwhere", "where ID in (" + ID + ")"), 
                new SqlParameter("@strTableName", "C_Supply") 
            };
            return SqlHelper.DeleteByWhere("SP_DeleteByWhere", param);
        }
        /// <summary>
        /// 根据行业分类删除供应信息
        /// </summary>
        /// <param name="ID">供应ID</param>
        /// <returns></returns>
        public int Delete(string ID)
        {
            SqlParameter[] param = new SqlParameter[] 
            {
                new SqlParameter("@strwhere", "where ID in (" + ID + ")"), 
                new SqlParameter("@strTableName", "C_Supply") 
            };
            return SqlHelper.DeleteByWhere("SP_DeleteByWhere", param);

        }
        /// <summary>
        /// 根据标题名称和会员名称得到供应信息
        /// </summary>
        /// <returns></returns>
        public DataTable GetDataTable(string strWhere)
        {
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strSelect","U_UserInfo.LoginName,U_UserInfo.CompanyName,U_UserInfo.QQ,C_Supply.*"),
                new SqlParameter("@strWhere","ON C_Supply.UserID =U_UserInfo.UserID "+strWhere),
                new SqlParameter("@strTableName","C_Supply INNER JOIN U_UserInfo"), 
                new SqlParameter("@strOrder", " order by AddDate  desc,ID desc")
            };
            return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_SelectByWhere", param);

        }
        /// <summary>
        /// 根据用户ID和审核信息得到求购信息
        /// </summary>
        /// <param name="ParamID"></param>
        /// <returns></returns>
        public DataTable GetDataTable(TSupply us)
        {
            string str = "where 1=1";
            if (us.UserID != 0)
                str += " and UserID=" + us.UserID;
            if (us.Verify != 0)
                str += " and Verify=" + us.Verify;
            else
                str += " and Verify=" + 1;

            SqlParameter[] param = new SqlParameter[] 
            {
                 new SqlParameter("@strwhere", str),
                new SqlParameter("@strtableName", "C_Supply"), 
                new SqlParameter("@strOrder", " order by AddDate  desc,ID desc") 
            };
            return SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_SelectByWhere", param);
        }
        /// <summary>
        /// 根据ID得到供应每一条信息
        /// </summary>
        /// <param name="ID"></param>
        /// <returns></returns>
        public TSupply GateItem(int ID)
        {
            SqlParameter[] param = new SqlParameter[] 
            { 
                new SqlParameter("@strWhere", "where ID=" +Convert.ToInt32( ID)), 
                new SqlParameter("@strTableName", "C_Supply"), 
                new SqlParameter("@strOrder", "") 
            };
            TSupply info = new TSupply();
            using (SqlDataReader reader = SqlHelper.ExecuteReader(CommandType.StoredProcedure, "SP_SelectByWhere", param))
            {
                if (!reader.Read())
                {
                    return info;
                }
                if (reader["UserID"].ToString() != "")
                {
                    info.ID = Convert.ToInt32(reader["ID"].ToString());
                }
                if (reader["UserID"].ToString() != "")
                {
                    info.UserID = Convert.ToInt32(reader["UserID"].ToString());
                }
                info.IndType = reader["IndType"].ToString();
                info.Name = reader["Name"].ToString();
                info.Key = reader["Key"].ToString();
                info.Info = (reader["Info"].ToString());
                info.Thumbnail = reader["Thumbnail"].ToString();
                info.Picture_1 = reader["Picture_1"].ToString();
                info.Picture_2 = reader["Picture_2"].ToString();
                info.Picture_3 = reader["Picture_3"].ToString();
                if (reader["ProType"].ToString() != "")
                {
                    info.ProType = Convert.ToInt32(reader["ProType"].ToString());
                }
                info.Place = reader["Place"].ToString();
                info.Brand = reader["Brand"].ToString();
                info.Model = reader["Model"].ToString();
                if (reader["Price"].ToString() != "")
                {
                    info.Price = Convert.ToDouble(reader["Price"].ToString());
                }
                if (reader["Quantity"].ToString() != "")
                {
                    info.Quantity = int.Parse(reader["Quantity"].ToString());
                }
                info.Units = (reader["Units"].ToString());
                if (reader["Minimum"].ToString() != "")
                {
                    info.Minimum = int.Parse(reader["Minimum"].ToString());
                }
                if (reader["Deadline"].ToString() != "")
                {
                    info.Deadline = int.Parse(reader["Deadline"].ToString());
                }
                if (reader["StartDate"].ToString() != "")
                {
                    info.StartDate = Convert.ToDateTime(reader["StartDate"].ToString());
                }
                if (reader["EndDate"].ToString() != "")
                {
                    info.EndDate = Convert.ToDateTime(reader["EndDate"].ToString());
                }

                info.Remark = reader["Remark"].ToString();
                info.Document = reader["Document"].ToString();
                if (reader["AddDate"].ToString() != "")
                {
                    info.AddDate = Convert.ToDateTime(reader["AddDate"].ToString());
                }
                else
                {
                    info.AddDate = DateTime.Now.Date;
                }
                if (reader["Verify"].ToString() != "")
                {
                    info.Verify = Convert.ToInt32(reader["Verify"].ToString());
                }
                if (reader["Recommend"].ToString() != "")
                {
                    info.Recommend = Convert.ToBoolean(reader["Recommend"].ToString());
                }
            }

            return info;
        }
        public bool GetMessageExists(string messagehead,int userid)
        {
            int num = 0;
            String sql = "select count(*)  from C_Supply where Name='" + messagehead + "' and UserID="+userid;
            SqlConnection conn = new SqlConnection(SqlHelper.ConnectionString);
            conn.Open();
            SqlCommand cmmd = new SqlCommand(sql, conn);
            num = Convert.ToInt32(cmmd.ExecuteScalar());
            conn.Close();
            if (num > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        public string TypeLink(string strTypeLink, string param)
        {
            if (strTypeLink[strTypeLink.Length - 1] == ',')
            {
                strTypeLink = strTypeLink.Substring(0, strTypeLink.Length - 1);
            }
            try
            {
                DataTable TypeData = new DataTable();
                if (param == "Detail")
                {
                    TypeData = Function.GetDataTable("Type,ID", "where ID in(" + strTypeLink + ") ", "W_IndustryType", " order by SuperiorsFlag  ");
                }
                else
                {
                    SqlParameter[] param1 = new SqlParameter[] 
                    {  
                        new SqlParameter("@ID",Convert.ToInt32(strTypeLink))
                    };
                    TypeData = SqlOperate.SqlHelper.ExecuteTable(CommandType.StoredProcedure, "SP_W_TypeLink", param1);
                }
                string strType = "";
                string str = "<a href=\"trade-";

                if (TypeData.Rows.Count > 0)
                {
                    for (int i = 0; i < TypeData.Rows.Count; i++)
                    {
                        strType += " > " + str;
                        strType += TypeData.Rows[i]["id"].ToString();
                        strType += Base.BasePage.Config.Rewrite + "\">";
                        strType += TypeData.Rows[i]["Type"].ToString();
                        strType += "</a>";
                    }
                }
                return strType;
            }
            catch (Exception ex)
            {
                return "";
            }
        }
        public DataTable GetSupplylist(int PageSize, int PageIndex, string strWhere)
        {
            //string strWhere = " U_UserInfo.Verify=1 AND U_UserInfo.GradeID=U_VipGrade.ID and datediff(hh,U_UserInfo.EndDate,getdate())>0";

            string strTable = " C_Supply left join U_UserInfo on C_Supply.UserID=U_UserInfo.UserID left join U_VipGrade on U_UserInfo.GradeID=U_VipGrade.ID ";
            int OrderType = 1;
            string OrderField = " U_VipGrade.Sort desc, C_Supply.AddDate ";
            string strSelect = "  C_Supply.*,U_UserInfo.UserID ,U_VipGrade.ID as VipID ";
            //int PageSize = 10;
            //int PageIndex = 1;
            if (HttpContext.Current.Request.QueryString["Page"] != null)
                PageIndex = Convert.ToInt32(HttpContext.Current.Request.QueryString["Page"]);
            if (PageIndex < 1) PageIndex = 1;
            int Count = 0;

            DataTable Lists = Pagination.GetDataTable(strWhere, strTable, OrderType, OrderField, strSelect, PageSize, PageIndex, Count, "C_Supply.ID");
            return Lists;
        }
        public DataTable GetSupplylist(string strWhere, string orderStr)
        {
            //string strsql = " SELECT  C_Supply.*,U_UserInfo.UserID,U_UserInfo.CompanyName ,U_UserInfo.LoginName ,U_UserInfo.Province ,U_UserInfo.City ,U_VipGrade.ID as VipID,U_VipGrade.Name as VipName ,U_UserOperate.SupplyCount,U_UserOperate.PurchaseCount,U_UserOperate.ExpoCount FROM C_Supply left join U_UserInfo on C_Supply.UserID=U_UserInfo.UserID left join U_VipGrade on U_UserInfo.GradeID=U_VipGrade.ID left join dbo.U_UserOperate on C_Supply.UserID=U_UserOperate.UserID  ";
            //if (strWhere != "")
            //{
            //    strsql += " where " + strWhere;
            //}
            //if (orderStr != "")
            //{
            //    strsql += orderStr;
            //}
            string strsql = " SELECT  C_Supply.ID,C_Supply.Thumbnail,C_Supply.[Name],C_Supply.AddDate,cast (C_Supply.Info as varchar(8000)) as Info,U_UserInfo.UserID,U_UserInfo.CompanyName ,U_UserInfo.LoginName ,U_UserInfo.Province ,U_UserInfo.City ,U_VipGrade.ID as VipID,U_VipGrade.Name as VipName ,count(C_Supply.ID) as SupplyCount FROM C_Supply left join U_UserInfo on C_Supply.UserID=U_UserInfo.UserID left join U_VipGrade on U_UserInfo.GradeID=U_VipGrade.ID left join dbo.U_UserOperate on C_Supply.UserID=U_UserOperate.UserID  ";
            if (strWhere != "")
            {
                strsql += " where " + strWhere;
            }
            strsql += " group by C_Supply.ID,C_Supply.Thumbnail,C_Supply.[Name],C_Supply.AddDate,cast (C_Supply.Info as varchar(8000)) ,U_UserInfo.UserID,U_UserInfo.CompanyName ,U_UserInfo.LoginName ,U_UserInfo.Province ,U_UserInfo.City ,U_VipGrade.ID ,U_VipGrade.Name ";
            if (orderStr != "")
            {
                strsql += orderStr;
            }
            DataTable dt = SqlHelper.ExecuteSqlQuery(strsql);
            return dt;
        }
        public DataTable GetNewExpo(int num)
        {
            DataTable dt = SqlHelper.ExecuteSqlQuery(" select top " + num + " a.ID,a.Title,a.AddDate,b.[Type],b.ID as typeid from W_Expo as a left join dbo.W_ExpoType as b on a.TypeID=b.ID order by AddDate desc");
            return dt;
        }
        public string lowerType(int strType, string p)
        {
            int TypeID = 0;
            if (HttpContext.Current.Request.QueryString["id"] != null && HttpContext.Current.Request.QueryString["id"] != "")
            {
                if (Base.Common.CheckNum(HttpContext.Current.Request.QueryString["ID"]))
                {
                    TypeID = Convert.ToInt32(HttpContext.Current.Request.QueryString["ID"]);
                }
            }


            StringBuilder ReBuilder = new StringBuilder();
            DataTable TypeDate = Function.GetDataTable(" distinct  a.[Type],a.ID,sequence,(select count(ID) from C_Supply where patindex('%,'+rtrim(a.ID)+',%',','+ IndType+',')>0 and (datediff(hh,EndDate,getdate())<0)) as countsupply ", " where ToRelease=1 AND SuperiorsID=" + TypeID + "  ", " W_IndustryType as a left join  C_Supply as b on patindex('%,'+rtrim(a.ID)+',%',','+ b.Indtype+',')>0", " order by sequence asc ");
            if (TypeDate.Rows.Count > 0)
            {
                ReBuilder.Append(@"<p class='ltnr2'>");
                for (int i = 0; i < TypeDate.Rows.Count; i++)
                {
                    ReBuilder.Append("");
                    ReBuilder.Append(@"<span><a title='" + TypeDate.Rows[i]["Type"].ToString() + "' href='../Supply/Trade-");
                    ReBuilder.Append(TypeDate.Rows[i]["ID"].ToString() + Base.BasePage.Config.Rewrite);
                    ReBuilder.Append(@"'>");
                    if (TypeDate.Rows[i]["Type"].ToString().Length > 7)
                    {
                        ReBuilder.Append(TypeDate.Rows[i]["Type"].ToString().Substring(0, 7));
                    }
                    else
                    {
                        ReBuilder.Append(TypeDate.Rows[i]["Type"].ToString());
                    }
                    ReBuilder.Append("(" + TypeDate.Rows[i]["countsupply"].ToString() + ")");
                    ReBuilder.Append(@"</a></span>");
                }
                ReBuilder.Append(@"</p>");
            }
            else
                ReBuilder.Append(@"<p class='ltnr2'><span>暂无下级分类！</span></p>");
            return ReBuilder.ToString();
        }
        //根据广告位获取广告信息
        public DataTable GetAD(int num, int adtypeID)
        {
            DataTable dt = SqlHelper.ExecuteSqlQuery("SELECT top " + num + " a.*,b.Ad_Width,b.Ad_Height FROM [W_Ad_MemberInfo] as a, [W_Ad_Table] as b WHERE a.T_ID=b.T_ID and a.T_ID=" + adtypeID);
            return dt;
        }
        public DataTable GetZDGQInfo(int num, string tabName, int isTop)
        {
            string sql = "select ";
            if (isTop == 1)
            {
                if (num > 0)
                {
                    sql += " top " + num + " ";
                }
                sql += " a.*,b.Province as area from " + tabName + " as a left join U_UserInfo as b on a.UserID=b.UserID where a.Verify=1 and (datediff(hh,a.EndDate,getdate())<0) order by adddate desc ";
            }
            else
            {
                sql += " TOP (7) a.*, b.Province AS area FROM  " + tabName + " AS a LEFT OUTER JOIN U_UserInfo AS b ON a.UserID = b.UserID WHERE     (a.ID NOT IN (SELECT     TOP (6) ID FROM " + tabName + " AS tab_1 where tab_1.Verify=1 and (datediff(hh,tab_1.EndDate,getdate())<0) order by adddate)) and a.Verify=1 and (datediff(hh,a.EndDate,getdate())<0) order by adddate";
            }
            DataTable dt = SqlHelper.ExecuteSqlQuery(sql);
            return dt;
        }

        public DataTable GetHotIndustry(int num)
        {
            DataTable dt = SqlHelper.ExecuteSqlQuery("select top " + num + " a.ID,a.[Type],count(b.ID) as supplycount from dbo.W_IndustryType as a,C_Supply as b where patindex('%,'+rtrim(a.ID)+',%',','+ b.IndType+',')>0 and (datediff(hh,b.EndDate,getdate()))<0 group by a.ID,a.[Type] order by count(b.ID) desc");
            return dt;
        }
        public DataTable GetListByUserID(string strWhere)
        {
            string strSql = " select * from dbo.C_Supply  ";
            if (strWhere != "")
            {
                strSql += " where " + strWhere;
            }
            DataTable dt = SqlHelper.ExecuteSqlQuery(strSql);
            return dt;
        }
        public DataTable GetcompanyCount(string strWhere)
        {
            string str = " select * from U_UserInfo";
            if (strWhere != "")
            {
                str += " where " + strWhere;
            }
            DataTable dt = SqlHelper.ExecuteSqlQuery(str);
            return dt;
        }
        #endregion

    }
}
